﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.OleDb;

namespace Ko2.DB
{

    public class MySql : MySQLBase
    {

        #region Members

        Ko2.Portal.Env _env = new Portal.Env();

        #endregion

        public Exception VerifyShopsNames(ref DataTable tbl, string colName)
        {
            return _env.VerifyShopsNames(ref tbl, colName);
        }

////////////////////////////////////////////////////////////////////////
/*
USE [DBMySQLTill]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_sel_trm_out_shift_close]
(@hours int, @days int)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

declare @cmd varchar(MAX);
	SET NOCOUNT ON;

set @cmd =	
'Execute(''  select s.number,c.date,uc.imported,tip.cash_id,tip.number,tip.name
from trm_out_shift_close c
inner join usr_trm_out_shift_close uc USING(cash_id,id)
inner join trm_out_shift_open s USING(cash_id,id)
inner join trm_in_pos tip ON tip.cash_id=c.cash_id
where date_add(c.date, INTERVAL 
 ' 
+ CAST( @hours as varchar(10) )
+ ' HOUR)<uc.imported
  AND date_add(uc.imported, INTERVAL ' 
+ CAST( @days as varchar(10) )
+ ' DAY)>NOW() ORDER BY uc.imported; '') at DBMYSQLTILL_ALLSHOPS ';
 
Execute(@cmd);

return 0;

END
*/
////////////////////////////////////////////////////////////////////////
/*
SELECT num, dt, dt_to_ax, cash_id, POS_num, name
FROM OPENQUERY(DBMYSQLTILL_ALLSHOPS, 
'
select s.number as num
, cast(c.date as char(800)) as dt
, cast(uc.imported as char(800)) as dt_to_ax
,tip.cash_id as cash_id, tip.number as POS_num, tip.name 
from ukmserver.trm_out_shift_close c
inner join ukmserver.usr_trm_out_shift_close uc USING(cash_id,id)
inner join ukmserver.trm_out_shift_open s USING(cash_id,id)
inner join ukmserver.trm_in_pos tip ON tip.cash_id=c.cash_id
where date_add(c.date, INTERVAL 1 HOUR)<uc.imported
    AND date_add(uc.imported, INTERVAL 3 DAY)>NOW()
ORDER BY uc.imported
') 
AS tbl
*/
////////////////////////////////////////////////////////////////////////

        public Exception Get_trm_out_shift_close(ref DataTable tbl, int hours_interval, int days_last)
        {
            Exception ret = null;
            string ConnStr = _env.GetAppSetting("DB04Connect");
            if (tbl == null)
                tbl = new DataTable("Tbl");
            try
            {
                using (OleDbConnection cn = new OleDbConnection(ConnStr.Replace("DB_Ex", "DBMySQLTill")))
                {
                    using (OleDbCommand myCMD = new OleDbCommand("sp_sel_trm_out_shift_close", cn))
                    {
                        cn.Open();
                        myCMD.CommandType = CommandType.StoredProcedure;
                        OleDbParameter parHours = myCMD.Parameters.Add("@hours", OleDbType.Integer);
                        parHours.Direction = ParameterDirection.Input;
                        OleDbParameter parDays = myCMD.Parameters.Add("@days", OleDbType.Integer);
                        parDays.Direction = ParameterDirection.Input;
                        parHours.Value = hours_interval;
                        parDays.Value = days_last;
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(myCMD))
                        {
                            adapter.Fill(tbl);
                        }
                    }
                    cn.Close();
                }
            }
            catch (Exception ex)
            {
                ret = new Exception(ex.Message, ex);
            }
            return ret;
        }
        public Exception GetTradeOnline(ref DataTable tbl, DateTime parDT)
        {
            Exception ret = null;
            string ConnStr = _env.GetAppSetting("DB04Connect");
            if (tbl == null)
                tbl = new DataTable("Tbl");
            DateTime dt = new DateTime(parDT.Year, parDT.Month, parDT.Day);
            try
            {
                using (OleDbConnection cn = new OleDbConnection(ConnStr.Replace("DB_Ex", "DBMySQLTill")))
                {
                    using (OleDbCommand myCMD = new OleDbCommand("Till_MYSQL_get_tills_data", cn))
                    {
                        cn.Open();

                        myCMD.CommandType = CommandType.StoredProcedure;

                        OleDbParameter date = myCMD.Parameters.Add("@dateStart", OleDbType.VarChar, 20);
                        date.Direction = ParameterDirection.Input;

                        OleDbParameter server = myCMD.Parameters.Add("@server", OleDbType.VarChar, 100);
                        server.Direction = ParameterDirection.Input;

                        date.Value = dt.ToString("yyyy-MM-dd");
                        server.Value = "DBMYSQLTILL_ALLSHOPS";

                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(myCMD))
                        {
                            adapter.Fill(tbl);
                        }
                    }
                    cn.Close();
                }
                if (tbl != null && tbl.Columns.Count > 0)
                {
                    tbl.Columns[0].ColumnName = @"Магазин";
                    tbl.Columns[0].Caption = @"Магазин";
                    tbl.Columns[1].ColumnName = @"Касса 1";
                    tbl.Columns[1].Caption = @"Касса 1";
                    tbl.Columns[2].ColumnName = @"Касса 2";
                    tbl.Columns[2].Caption = @"Касса 2";
                    tbl.Columns[3].ColumnName = @"Касса 3";
                    tbl.Columns[3].Caption = @"Касса 3";
                    tbl.Columns[4].ColumnName = @"Касса 4";
                    tbl.Columns[4].Caption = @"Касса 4";
                    tbl.Columns[5].ColumnName = @"Итого";
                    tbl.Columns[5].Caption = @"Итого";
                }
                ret = VerifyShopsNames(ref tbl, "Магазин");
            }
            catch (Exception ex)
            {
                ret = new Exception(ex.Message, ex);
            }
            return ret;
        }

        public Exception GetShopNames(ref DataTable tbl)
        {
            Exception ret = null;
            string ConnStr = _env.GetAppSetting("DB04Connect");
            if (tbl == null)
                tbl = new DataTable("Tbl");
            try
            {
                using (OleDbConnection cn = new OleDbConnection(ConnStr.Replace("DB_Ex", "DBMySQLTill")))
                {
                    using (OleDbCommand myCMD = new OleDbCommand("sp_sel_ShopList", cn))
                    {
                        cn.Open();
                        myCMD.CommandType = CommandType.StoredProcedure;
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(myCMD))
                        {
                            adapter.Fill(tbl);
                        }
                    }
                    cn.Close();
                }
                ret = VerifyShopsNames(ref tbl, "ShopName");
            }
            catch (Exception ex)
            {
                ret = new Exception(ex.Message, ex);
            }
            return ret;
        }
        public Exception GetTillDocs_KM3(ref DataTable tbl, DateTime parDT)
        {
            /*
            Call usr_km3();
            Передаёт до 10 документов
            В солбцах cash_id,shift_id передаёт ид кассы и ид смены, при успешном приёме и обработке записи вызвать:
            Call usr_update_km3_log(cash_id,shift_id);
            */

            Exception ret = null;
            string ConnStr = _env.GetAppSetting("DB04Connect");
            if (tbl == null)
                tbl = new DataTable("Tbl");
            try
            {
                ConnStr = ConnStr.Replace("DB_Ex", "DBMySQLTill");
                if (ConnStr.IndexOf(@"General Timeout", 0) < 0)
                {
                    if (ConnStr[ConnStr.Length - 1] != ';')
                        ConnStr += ";";
                    ConnStr += @"General Timeout=65000;Connect Timeout=65000;";
                }
                using (OleDbConnection cn = new OleDbConnection(ConnStr))
                {
                    using (OleDbCommand myCMD = new OleDbCommand("sp_sel_TillDocs_KM3", cn))
                    {
                        cn.Open();
                        myCMD.CommandType = CommandType.StoredProcedure;
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(myCMD))
                        {
                            adapter.Fill(tbl);
                        }
                        VerifyShopsNames(ref tbl, "shop");
                    }
                    cn.Close();
                }
            }
            catch (Exception ex)
            {
                ret = new Exception(ex.Message, ex);
            }
            return ret;
        }
        public Exception GetTillDocs_KM3_Close(Int64 par_cash_id, Int64 par_shift_id)
        {
            /*
            Call usr_km3();
            Передаёт до 10 документов
            В солбцах cash_id,shift_id передаёт ид кассы и ид смены, при успешном приёме и обработке записи вызвать:
            Call usr_update_km3_log(cash_id,shift_id);
            */
            Exception ret = null;
            string ConnStr = _env.GetAppSetting("DB04Connect");
            try
            {
                using (OleDbConnection cn = new OleDbConnection(ConnStr.Replace("DB_Ex", "DBMySQLTill")))
                {
                    using (OleDbCommand myCMD = new OleDbCommand("sp_sel_TillDocs_KM3_Close", cn))
                    {
                        cn.Open();
                        myCMD.CommandType = CommandType.StoredProcedure;

                        OleDbParameter cash_id = myCMD.Parameters.Add("@cash_id", OleDbType.VarChar, 20);
                        cash_id.Direction = ParameterDirection.Input;
                        cash_id.Value = par_cash_id.ToString();

                        OleDbParameter shift_id = myCMD.Parameters.Add("@shift_id", OleDbType.VarChar, 20);
                        shift_id.Direction = ParameterDirection.Input;
                        shift_id.Value = par_shift_id.ToString();

                        int result = myCMD.ExecuteNonQuery();
                    }
                    cn.Close();
                }
            }
            catch (Exception ex)
            {
                ret = new Exception(ex.Message, ex);
            }
            return ret;
        }
        public Exception GetTillDocs_KM6(ref DataTable tbl, DateTime parDT)
        {
/*
SELECT
    CONCAT(t.enterprise_name,' ',t.enterprise_address)AS OrgAddr_Line1,
    CONCAT(_utf8'Обособленное подразделение "',t.account_name,'"')AS OrgDeparment,
    t.okpo,
    t.inn,
    _utf8'СП101ФР-К' AS POS_Type_Description,
    _utf8'УКМ' AS POS_Programm_Name,
    t.kkm_serial_number AS POS_Manufacture_No,
    t.kkm_reg_num AS POS_Register_No,
    t.user_name AS Operator_FIO,
    t.kkm_shift_number AS Smena,
    t.oper_number AS Doc_No,
    DATE_FORMAT(t.shift_close_date,'%d.%m.%Y')AS Doc_Date,
    DATE_FORMAT(t.first_receipt_date,'%H:%i')AS Time_In,
    DATE_FORMAT(t.shift_close_date,'%H:%i')AS Time_Out,
    DATE_FORMAT(t.shift_close_date,'%d')AS Doc2_Day,
    ELT(MONTH(t.shift_close_date),_utf8'января',_utf8'февраля',_utf8'марта',_utf8'апреля',_utf8'мая',_utf8'июня',_utf8'июля',_utf8'августа',_utf8'сентября',_utf8'октября',_utf8'ноября',_utf8'декабря')AS Doc2_Month,
    DATE_FORMAT(t.shift_close_date,'%Y')AS Doc2_Year,
    '0,00' AS Cel_05,
    REPLACE(ROUND(t.total_payments,2),'.',',')AS Cel_06,
    REPLACE(ROUND(t.cash_sale,2),'.',',')AS Cel_07,
    REPLACE(ROUND(t.cash_returns,2),'.',',')AS Cel_08,
    CONCAT(_utf8'П/К: ',REPLACE(ROUND(t.nonecash_sale,2),'.',','))AS Cel_06_pk,
    t.user_name AS Cel_09,
    REPLACE(ROUND(t.cash_sale,2),'.',',')AS Amount_Total_1,
    REPLACE(ROUND(t.cash_returns,2),'.',',')AS Amount_Total_2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(t.literal_cash_sale,_utf8' рубл',1),' ',4)AS Amount_Propis_1,
    TRIM(SUBSTRING_INDEX(REPLACE(t.literal_cash_sale,SUBSTRING_INDEX(t.literal_cash_sale,' ',4),''),_utf8' рубл',1))AS Amount_Propis_2,
    RIGHT(t.literal_cash_sale,2)AS Amount_Propis_3,
    SUBSTRING_INDEX(t.director,':',1)AS BOSS_Status,
    SUBSTRING_INDEX(t.director,':',-1)AS BOSS_Name
FROM
(SELECT t.*,ln_literal_notation(t.cash_sale,0,0)AS literal_cash_sale
FROM
(SELECT t.*,sc.id AS shift_id,
    sc.kkm_shift_number,sc.kkm_serial_number,
    IFNULL(
    CASE WHEN sc.kkm_serial_number='102344' THEN '23590/2' ELSE CASE WHEN sc.kkm_serial_number='102345' THEN '23590/1' ELSE
    CASE WHEN sc.kkm_serial_number='AKK061701149' THEN '22099/2' ELSE CASE WHEN sc.kkm_serial_number='AKK061701151' THEN '22099/1' ELSE
    CASE WHEN sc.kkm_serial_number='AIK054301540' THEN '22703/1' ELSE CASE WHEN sc.kkm_serial_number='AIK054301390' THEN '22703/2' ELSE
    CASE WHEN sc.kkm_serial_number='AKK062101259' THEN '17947/1' ELSE CASE WHEN sc.kkm_serial_number='AKK060201288' THEN '17947/2' ELSE sc.kkm_registration_number END END END END END END END END,'')AS kkm_reg_num,
    SUM(p.amount*(h.type IN(0,5,8,1,4,9))*(p.payment_id NOT IN(21)))AS total_payments,
    SUM(p.amount*(h.type IN(0,5,8))*(p.payment_id IN(0)))AS cash_sale,
    SUM(p.amount*(h.type IN(1,4,9))*(p.payment_id IN(0)))AS cash_returns,
    SUM(p.amount*(1-2*(h.type IN(1,4,9)))*(p.payment_id NOT IN(0,21)))AS nonecash_sale,
    h.date AS first_receipt_date,
    tis.inn,
    tis.okpo,
    tis.enterprise_name,
    tis.enterprise_address,
    l.user_name,
    tis.director
FROM
(SELECT
    laa.id AS account_id,
    SUBSTRING_INDEX(laa.name,' (',1)AS account_name,
    lao.number AS oper_number,
    lao.operation_date AS shift_close_date,
    lao.amount AS oper_amount,
    tip.cash_id,
    tip.store_id
FROM trm_in_account_type lat
INNER JOIN local_auth_account laa ON laa.account_type_id=lat.id
INNER JOIN local_auth_operation lao ON lao.account_id=laa.id
INNER JOIN local_auth_account_journal laaj ON laaj.account_id=laa.id AND laaj.source_id=lao.id
INNER JOIN trm_in_pos tip ON INSTR(lao.description,tip.name)>0 AND tip.store_id=SUBSTRING_INDEX(laa.params,',',1)
WHERE
    lat.params LIKE '%moz:2%'
    AND laaj.source_type=6
    AND tip.deleted=0
    AND lao.operation_date>=__datetime)t
INNER JOIN trm_in_store tis USING(store_id)
INNER JOIN trm_out_shift_close sc ON sc.cash_id=t.cash_id AND sc.date=t.shift_close_date
INNER JOIN trm_out_login l ON l.cash_id=sc.cash_id AND l.id=sc.login
INNER JOIN trm_in_users u ON u.store_id=tis.store_id AND u.id=l.user_id
INNER JOIN trm_out_shift_open so ON so.cash_id=sc.cash_id AND so.id=sc.id
INNER JOIN trm_out_receipt_header h ON h.cash_id=so.cash_id AND h.shift_open=so.id
INNER JOIN trm_out_receipt_footer f ON f.cash_id=h.cash_id AND f.id=h.id
INNER JOIN trm_out_receipt_payment p ON p.cash_id=h.cash_id AND p.receipt_header=h.id
WHERE
    f.result=0 AND h.type IN(0,5,8,1,4,9)
    AND p.type=0
GROUP BY h.cash_id,so.id)t )t;
             
*/

/*
-- call usr_km6('2012-03-05 22:10:00')
Call usr_km6();
Передаёт до 10 документов
В солбцах cash_id,shift_id передаёт ид кассы и ид смены, при успешном приёме и обработке записи вызвать:
Call usr_update_km6_log(cash_id,shift_id);

*/

            Exception ret = null;
            string ConnStr = _env.GetAppSetting("DB04Connect");
            if (tbl == null)
                tbl = new DataTable("Tbl");
            try
            {
                ConnStr = ConnStr.Replace("DB_Ex", "DBMySQLTill");
                if (ConnStr.IndexOf(@"General Timeout", 0) < 0)
                {
                    if (ConnStr[ConnStr.Length-1] != ';')
                        ConnStr += ";";
                    ConnStr += @"General Timeout=65000;Connect Timeout=65000;";
                }
                using (OleDbConnection cn = new OleDbConnection(ConnStr))
                {
                    using (OleDbCommand myCMD = new OleDbCommand("sp_sel_TillDocs_KM6", cn))
                    {
                        cn.Open();
                        myCMD.CommandType = CommandType.StoredProcedure;
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(myCMD))
                        {
                            adapter.Fill(tbl);
                        }
                        VerifyShopsNames(ref tbl, "shop");
                    }
                    cn.Close();
                }
            }
            catch (Exception ex)
            {
                ret = new Exception(ex.Message, ex);
            }
            return ret;
        }
        public Exception GetTillDocs_KM6_Close(Int64 par_cash_id, Int64 par_shift_id)
        {
/*
-- call usr_km6('2012-03-05 22:10:00')
Call usr_km6();
Передаёт до 10 документов
В солбцах cash_id,shift_id передаёт ид кассы и ид смены, при успешном приёме и обработке записи вызвать:
Call usr_update_km6_log(cash_id,shift_id);

*/
            Exception ret = null;
            string ConnStr = _env.GetAppSetting("DB04Connect");
            try
            {
                using (OleDbConnection cn = new OleDbConnection(ConnStr.Replace("DB_Ex", "DBMySQLTill")))
                {
                    using (OleDbCommand myCMD = new OleDbCommand("sp_sel_TillDocs_KM6_Close", cn))
                    {
                        cn.Open();
                        myCMD.CommandType = CommandType.StoredProcedure;

                        OleDbParameter cash_id = myCMD.Parameters.Add("@cash_id", OleDbType.VarChar, 20);
                        cash_id.Direction = ParameterDirection.Input;
                        cash_id.Value = par_cash_id.ToString();

                        OleDbParameter shift_id = myCMD.Parameters.Add("@shift_id", OleDbType.VarChar, 20);
                        shift_id.Direction = ParameterDirection.Input;
                        shift_id.Value = par_shift_id.ToString();

                        int result = myCMD.ExecuteNonQuery();
                    }
                    cn.Close();
                }
            }
            catch (Exception ex)
            {
                ret = new Exception(ex.Message, ex);
            }
            return ret;
        }

    }

}
